package com.example.demo.wuliu;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.example.demo.bean.importexcelpojo;
import com.example.demo.bean.importxishuexcelpojo;
import com.example.demo.datasource.multi.dao.call_procedure;
import com.example.demo.datasource.multi.dao.excelDao;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;

@Slf4j
@Controller
public class wuliucontroller {
    @Autowired
    wuliuservice wuliuservice;

    @Autowired
    addwuliuservice addwuliuservice;

    @Autowired
    excelDao excelDao;

    @Autowired
    importexcelpojo importexcelpojo;

    @Autowired
    call_procedure call_procedure;

    @ResponseBody
    @RequestMapping("/getflag")
    public JSONObject getflag(){

        String  table = "SELECT DISTINCT flag FROM wuliuyunfei" ;
        List tablelist = wuliuDao.findflag(table);
        System.out.println(tablelist);

        Map<String,Object> map = new HashMap<>();
        map.put("mag",1);
        map.put("result",tablelist);

        JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

        return wuliupojos;
    }


    @ResponseBody
    @RequestMapping("/importexcel")
    public layuitablepojo importexcel(MultipartFile file, HttpServletRequest request){

        String fname = "";
        List faildlist = new ArrayList();
        List successlist = new ArrayList();
        List faildreasonlist = new ArrayList();
        Map<Object,String> faildreason = new HashMap<>();
        if (file != null) {
            String filename = file.getOriginalFilename();
            /*String substring = filename.substring(filename.lastIndexOf(".") + 1);
            substring = substring.toLowerCase();
            if (!substring.equals("xls") || substring.equals("xlsx")) {
                log.info("非excel文件");
            }*/
            if (filename.equals("mubanroot.xlsx")){
                filename = "mubanroot_other.xlsx";
            }

            log.info("文件名："+filename);

            String path=System.getProperty("user.dir");
            File dir=new File(path+"\\file");
            if (!dir.exists()){
                dir.mkdir();
            }
            File newFile = new File(path+"\\file", filename);
            String newfilename = path+"\\file\\"+filename;
            log.info("文件："+newfilename);
            try {
                file.transferTo(newFile);
            } catch (IOException e) {
                e.printStackTrace();
            }
            fname = newfilename;
        }

        String dbFileName = "src/main/resources/dbFile/";
        // 写法1：JDK8+ ,不用额外写一个DemoDataListener
        // since: 3.0.0-beta1
        String fileName = dbFileName + "sql.xls";
        // 写法2：
        // 匿名内部类 不用额外写一个DemoDataListener
        fileName = dbFileName + "sql.xls";

        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fname, importexcelpojo.class, new ReadListener<importexcelpojo>() {
            /**
             * 单次缓存的数据量
             */
            public static final int BATCH_COUNT = 100;
            /**
             *临时存储
             */
            private List<importexcelpojo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            @Override
            public void invoke(importexcelpojo data, AnalysisContext context) {
                cachedDataList.add(data);
                if (cachedDataList.size() >= BATCH_COUNT) {
                    saveData();
                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {

                saveData();
            }

            /**
             * 加上存储数据库
             */
            private void saveData() {
                log.info("{}条数据，开始存储数据库！", cachedDataList.size());
                    for (importexcelpojo data:cachedDataList
                    ) {
                        log.info("读取到一条数据{}", JSON.toJSONString(data));
                    /*String sql = "INSERT INTO excel (uid,name,phone,depNo,code,depInfo) VALUES ('"+data.getUid()+"','"+data.getName()+"','"+data.getPhone()+
                            "',"+data.getDepNo()+","+data.getCode()+",'"+data.getDepInfo()+"')";*/
                        String  table1 = "SELECT wuliuname FROM lctp where wuliuname = '"+data.getDeliveryname()+"'";
                        String tablelist = wuliuDao.findwuliuname(table1);
                        System.out.println(tablelist);

                        if (null != tablelist){
                            String  table = "SELECT * FROM wuliuyunfei a WHERE a.deliveryname = '" +
                                    data.getDeliveryname() +
                                    "' AND a.sendcity = '" + data.getSendcity()/*+"市"*/ +
                                    "' AND a.receiveprovince = '" + data.getReceiveprovince() +
                                    "' AND a.receivecity = '" + data.getReceivecity()  +
                                    "' AND a.wu = " + data.getWu() +
                                    "  AND a.wd = " + data.getWd() ;
                            List<tablepojo> addresult = wuliuDao.findtable(table);
                            String ss= String.valueOf(addresult);
                            System.out.println(ss);
                            System.out.println(addresult);
                            if (ss.equals("[]")){
                                try {
                                    int d=excelDao.saveone1(data);
                                    System.out.println("返回id值："+data.getId());
                                    if (!(d ==1)){
                                        faildlist.add(data);
                                        faildreason.put(data,"插入失败，请联系开发人员");


                                    }else {
                                        successlist.add(data.getId());
                                    }
                                }catch (Exception e){
                                    System.out.println(e.getMessage());
                                    faildlist.add(data);
                                    faildreason.put(data,"sql执行失败");


                                }

                            }else {
                                faildlist.add(data);
                                faildreason.put(data,"物流路线数据重复！请检查");

                            }
                        }else {
                            faildlist.add(data);
                            faildreason.put(data,"物流公司不存在，请先新增相应的物流公司");

                        }



                    }

                //mysql批量插入，注意要在数据库连接字符串加上  &allowMultiQueries=true&rewriteBatchedStatements=true
                //int dd = excelDao.save(cachedDataList);
                log.info("存储数据库成功！");
            }
        }).sheet().doRead();

        System.out.println(faildlist.toString());
        String faildresult = faildlist.toString();
        faildresult= faildresult.substring(1,faildresult.length());
        faildresult= faildresult.substring(0,faildresult.length()-1);
        System.out.println(faildresult);

        Map<String,Object> map = new HashMap<>();
        map.put("false",faildlist);
        map.put("success",successlist);

        map.put("faildreason",faildreason);
        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("存储数据库成功！");
        layui.setData(map);

        return layui;
    }

    @Autowired
    com.example.demo.bean.importxishuexcelpojo importxishuexcelpojo;
    @ResponseBody
    @RequestMapping("/importxishuexcel")
    public layuitablepojo importxishuexcel(MultipartFile file, HttpServletRequest request){

        //String fname = "D:\\workspace\\idea\\wuliu\\file\\lctp.xlsx";
        String fname = "";
        List faildlist = new ArrayList();
        List successlist = new ArrayList();
        List faildreasonlist = new ArrayList();
        Map<Object,String> faildreason = new HashMap<>();
        if (file != null) {
            String filename = file.getOriginalFilename();
            log.info("文件名："+filename);
            String path=System.getProperty("user.dir");
            File dir=new File(path+"\\file");
            if (!dir.exists()){
                dir.mkdir();
            }
            File newFile = new File(path+"\\file", filename);
            String newfilename = path+"\\file\\"+filename;
            log.info("文件："+newfilename);
            try {
                file.transferTo(newFile);
            } catch (IOException e) {
                e.printStackTrace();
            }
            fname = newfilename;
        }

        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fname, importxishuexcelpojo.class, new ReadListener<importxishuexcelpojo>() {
            /**
             * 单次缓存的数据量
             */
            public static final int BATCH_COUNT = 100;
            /**
             *临时存储
             */
            private List<importxishuexcelpojo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            @Override
            public void invoke(importxishuexcelpojo data, AnalysisContext context) {
                cachedDataList.add(data);
                if (cachedDataList.size() >= BATCH_COUNT) {
                    saveData();
                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {

                saveData();
            }

            /**
             * 加上存储数据库
             */
            private void saveData() {
                log.info("{}条数据，开始存储数据库！", cachedDataList.size());
                    for (importxishuexcelpojo data:cachedDataList
                    ) {
                        log.info("读取到一条数据{}", JSON.toJSONString(data));

                        String  table1 = "SELECT wuliucode FROM lctp where wuliucode = "+data.getWuliucode()+"";
                        String tablelist = wuliuDao.findwuliuname(table1);
                        //System.out.println(tablelist);

                        String  table2 = "SELECT wuliuname FROM lctp where wuliuname = '"+data.getWuliuname()+"'";
                        String tablelist2 = wuliuDao.findwuliuname(table2);
                       // System.out.println(tablelist2);


                        String  table3 = "SELECT pname FROM lctp where pname = '"+data.getPname()+"'";
                        String tablelist3 = wuliuDao.findwuliuname(table3);
                        //System.out.println(tablelist3);

                        if (null != tablelist){
                            System.out.println("公司编码已存在！");
                            faildlist.add(data);
                            faildreason.put(data,"插入失败，公司编码已存在！");
                            continue;
                        }

                        if (null != tablelist2){
                            System.out.println("公司名称已存在！");
                            faildlist.add(data);
                            faildreason.put(data,"插入失败，公司名称已存在！");
                            continue;
                        }

                        if (null != tablelist3){
                            System.out.println("公司简称已存在！");
                            faildlist.add(data);
                            faildreason.put(data,"插入失败，公司简称已存在！");
                            continue;
                        }
                        try {
                            int d=excelDao.saveone2(data);
                            System.out.println("返回id值："+data.getId());
                            if (!(d ==1)){
                                faildlist.add(data);
                                faildreason.put(data,"插入失败，请联系开发人员");

                            }else {
                                successlist.add(data.getId());
                            }
                        }catch (Exception e){
                            System.out.println(e.getMessage());
                            faildlist.add(data);
                            faildreason.put(data,"sql执行失败");

                        }
                    }

                //mysql批量插入，注意要在数据库连接字符串加上  &allowMultiQueries=true&rewriteBatchedStatements=true
                //int dd = excelDao.save(cachedDataList);
                log.info("存储数据库成功！");
            }
        }).sheet().doRead();

        System.out.println(faildlist.toString());
        String faildresult = faildlist.toString();
        faildresult= faildresult.substring(1,faildresult.length());
        faildresult= faildresult.substring(0,faildresult.length()-1);
        System.out.println(faildresult);

        Map<String,Object> map = new HashMap<>();
        map.put("false",faildlist);
        map.put("success",successlist);

        map.put("faildreason",faildreason);
        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("存储数据库成功！");
        layui.setData(map);

        return layui;
    }

    @ResponseBody
    @RequestMapping("/wuliupojos")
    public JSONObject wuliupojos(double length, double wight, double height, String sendcity, String receiveprovince, String receivecity, double asw,String selectvalue){
        System.out.println(length+"||"+wight+"||"+height+"||"+sendcity+"||"+receiveprovince+"||"+receivecity+"||"+asw+"||"+selectvalue);
        if (sendcity.equals("龙岩")){
            sendcity="龙岩市";
        }

        JSONObject wuliupojos = wuliuservice.qurywuliu(length,wight,height,sendcity,receiveprovince,receivecity,asw,selectvalue);

        return wuliupojos;
    }

    @Autowired
    private com.example.demo.datasource.multi.dao.wuliuDao wuliuDao;
    @ResponseBody
    @RequestMapping("/gettable")
    public JSONObject gettable(){

        String  table = "SELECT * FROM wuliuyunfei" ;
        List<tablepojo> tablelist = wuliuDao.findtable(table);
        System.out.println(tablelist);

        Map<String,Object> map = new HashMap<>();
        map.put("mag",1);
        map.put("result",tablelist);

        JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

        return wuliupojos;
    }


    @ResponseBody
    @RequestMapping("/getwuliutable")
    public layuitablepojo getwuliutable(HttpServletRequest request, Page page){

        int limit = page.getLimit();
        int since = (page.getPage()-1)*page.getLimit();

        String  table = "select * from wuliuyunfei ORDER BY id ASC limit "+since+","+limit ;
        List<tablepojo> tablelist = wuliuDao.findtable(table);
        System.out.println(tablelist);

        String  table1 = "select id from wuliuyunfei" ;
        List<tablepojo> tablelist1 = wuliuDao.findtable(table1);
        System.out.println(tablelist1.size());//数据总条数

        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("");
        layui.setCount((long)tablelist1.size());
        layui.setData(tablelist);

        return layui;
    }

    @ResponseBody
    @RequestMapping("/exportFile")
    public layuitablepojo exportFile(HttpServletRequest request){



        String  table = "select * from wuliuyunfei" ;
        List<tablepojo> tablelist = wuliuDao.findtable(table);
        System.out.println(tablelist);

        String  table1 = "select id from wuliuyunfei" ;
        List<tablepojo> tablelist1 = wuliuDao.findtable(table1);
        System.out.println(tablelist1.size());//数据总条数

        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("");
        layui.setCount((long)tablelist1.size());
        layui.setData(tablelist);

        return layui;
    }

    @ResponseBody
    @RequestMapping("/exportxishuFile")
    public layuitablepojo exportxishuFile(HttpServletRequest request){

        String  table = "SELECT * FROM lctp" ;
        List<xishupojo> tablelist = wuliuDao.findxishutable(table);
        System.out.println(tablelist);

        Map<String,Object> map = new HashMap<>();
        map.put("mag",1);
        map.put("result",tablelist);

        JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("");
        layui.setCount((long)tablelist.size());
        layui.setData(tablelist);

        return layui;
    }

    @Autowired
    com.example.demo.bean.exportecxelpojo exportecxelpojo;

    @ResponseBody
    @RequestMapping("/exportexcel")
    public void exportexcel(HttpServletRequest request, HttpServletResponse response){

        String path=System.getProperty("user.dir");
        File dir=new File(path+"\\file");
        if (!dir.exists()){
            dir.mkdir();
        }

        String filename = System.currentTimeMillis() + ".xlsx";
        String newfilename = path+"\\file\\"+filename;
        log.info("文件："+newfilename);
        EasyExcel.write(newfilename, tablepojo.class)
                .sheet("测试导出")
                .doWrite(() -> {
                    // 分页查询数据
                    String  table = "select * from wuliuyunfei" ;
                    List<tablepojo> tablelist = wuliuDao.findtable(table);
                    System.out.println(tablelist);
                    return tablelist;
                });

        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试导出.xlsx", "utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        //4. 创建输入、输出流
        FileInputStream input = null;
        try {
            input = new FileInputStream(newfilename);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        ServletOutputStream sos = null;
        try {
            sos = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        byte[] arr;
        //IO流获取文件的字节流，然后再响应给浏览器
        try {
            arr = new byte[1024];
            int res = 0;
            while((res = input.read(arr)) > 0){
                //将读取的内容输出到输出流中
                sos.write(arr, 0, res);
            }

            input.close();
            sos.close();
        }catch (Exception e){

        }

    }

    @ResponseBody
    @RequestMapping("/getwuliutablebyid")
    public layuitablepojo getwuliutablebyid(HttpServletRequest request, Page page,String idlist){

        int up = page.getPage()*page.getLimit();
        int down = (page.getPage()-1)*page.getLimit();
        //String idlist= "[\"1\",\"2\"]";
        System.out.println(idlist);
        List<String> list = JSON.parseArray(idlist, String.class);
        String Sid = "";
        for (int i= 0 ;list.size()>i;i++){
            if (i==list.size()-1){
                Sid=Sid+"'"+list.get(i)+"'";
            }else {
                Sid=Sid+"'"+list.get(i)+"',";
            }

        }
        System.out.println(Sid);

        String  table = "select * from wuliuyunfei where id in ("+Sid+")" +
                " ORDER BY id ASC limit "+down+","+up ;
        List<tablepojo> tablelist = wuliuDao.findtable(table);
        System.out.println(tablelist);

        String  table1 = "select id from wuliuyunfei where id in ("+Sid+")" ;
        List<tablepojo> tablelist1 = wuliuDao.findtable(table1);
        System.out.println(tablelist1.size());//数据总条数

        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("");
        layui.setCount((long)tablelist1.size());
        layui.setData(tablelist);

        return layui;
    }
    @ResponseBody
    @RequestMapping("/getxishutablebyid")
    public layuitablepojo getxishutablebyid(HttpServletRequest request, Page page,String idlist){

        int up = page.getPage()*page.getLimit();
        int down = (page.getPage()-1)*page.getLimit();
        //String idlist= "[\"1\",\"2\"]";
        System.out.println(idlist);
        List<String> list = JSON.parseArray(idlist, String.class);
        String Sid = "";
        for (int i= 0 ;list.size()>i;i++){
            if (i==list.size()-1){
                Sid=Sid+"'"+list.get(i)+"'";
            }else {
                Sid=Sid+"'"+list.get(i)+"',";
            }

        }
        System.out.println(Sid);

        String  table = "select * from lctp where id in ("+Sid+")" +
                " ORDER BY id ASC limit "+down+","+up ;
        List<xishupojo> tablelist = wuliuDao.findxishutable(table);
        System.out.println(tablelist);

        String  table1 = "select id from wuliuyunfei where id in ("+Sid+")" ;
        List<xishupojo> tablelist1 = wuliuDao.findxishutable(table1);
        System.out.println(tablelist1.size());//数据总条数

        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("");
        layui.setCount((long)tablelist1.size());
        layui.setData(tablelist);

        return layui;
    }

    @ResponseBody
    @RequestMapping("/searchwuliutable")
    public layuitablepojo searchwuliutable(HttpServletRequest request, Page page,String selectvalue,String inputvalue){
        int up = page.getPage()*page.getLimit();
        int down = (page.getPage()-1)*page.getLimit();

        String  table = "select * from wuliuyunfei where "+selectvalue+" like '"+inputvalue+"%' " +
                " ORDER BY id ASC limit "+down+","+up ;;
        List<tablepojo> tablelist = wuliuDao.findtable(table);
        System.out.println(tablelist);

        String  table1 = "select id from wuliuyunfei where "+selectvalue+" like '"+inputvalue+"%' " ;
        List<tablepojo> tablelist1 = wuliuDao.findtable(table1);
        System.out.println(tablelist1.size());//数据总条数

        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("");
        layui.setCount((long)tablelist1.size());
        layui.setData(tablelist);

        return layui;
    }

    @Autowired
    updatetablepojo updatetablepojo;

    @ResponseBody
    @RequestMapping("/upwuliutable")
    public JSONObject upwuliutable(String sdata,int mode){
        if (mode == 1){
            updatetablepojo dd = JSON.parseObject(sdata, updatetablepojo.class);

            String  table = "UPDATE wuliuyunfei a set a.fww= " + dd.getFww() +
                    ",a.fwp=" + dd.getFwp() +
                    ",a.wu=" + dd.getWu() +
                    ",a.wd=" + dd.getWd() +
                    ",a.wp=" + dd.getWp() +
                    ",a.fw=" + dd.getFw() +
                    ",a.discount=" + dd.getDiscount() +
                    ",a.discounttype=" + dd.getDiscounttype()+
                    ",a.flag='"+dd.getFlag()+"'"+
                    " WHERE ID = " + dd.getId();
            int upresult = wuliuDao.upwuliu(table);
            System.out.println(upresult);

            Map<String,Object> map = new HashMap<>();
            map.put("msg",1);
            map.put("result",upresult);

            JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

            return wuliupojos;
        }else {
            int result=0;
            System.out.println(sdata);
            List<updatetablepojo> dd = JSON.parseArray(sdata, updatetablepojo.class);
            for (updatetablepojo up:dd
                 ) {
                String  table = "UPDATE wuliuyunfei a set a.fww= " + up.getFww() +
                        ",a.fwp=" + up.getFwp() +
                        ",a.wu=" + up.getWu() +
                        ",a.wd=" + up.getWd() +
                        ",a.wp=" + up.getWp() +
                        ",a.fw=" + up.getFw() +
                        ",a.discount=" + up.getDiscount() +
                        ",a.discounttype=" + up.getDiscounttype()+
                        ",a.flag='"+up.getFlag()+"'"+
                        " WHERE ID = " + up.getId();
                int upresult = wuliuDao.upwuliu(table);
                System.out.println(upresult);
                result++;
            }

            Map<String,Object> map = new HashMap<>();
            map.put("msg",1);
            map.put("result",result);

            JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

            return wuliupojos;
        }

    }

    @ResponseBody
    @RequestMapping("/addwuliudata")
    public JSONObject addwuliudata(String map,String mapList,String fromprovince,String sendcity, String receiveprovince, String receivecity,
                                   String sfcztp,String sfbk,String sfly,String jdths,String jdtks,String lctparabolic,
                                   String fww,String fwp,String wu,String wd,String wp,String fw,String discount,String discounttype,String flag){
        System.out.println(fromprovince+sendcity+receiveprovince+receivecity+sfcztp+sfbk+sfly+jdths+jdtks+lctparabolic+fww+fwp+wu+wd+wp+fw+discount+discounttype);
        sendcity=sendcity+"市";
        /*转换map格式的json*/
        System.out.println(map);
        Map maps = (Map)JSON.parse(map);
        //System.out.println(maps.get("sfbk"));
        /*转换list格式的json*/
        System.out.println("String:"+mapList);
        List<String> list = JSON.parseArray(mapList, String.class);
        System.out.println("list:"+list);
        List<String> toedit=new ArrayList<>();
        List<String> canadd=new ArrayList<>();
        List<Integer> toeditid=new ArrayList<>();
        List<Integer> canaddid=new ArrayList<>();
        for (String list1:list
             ) {
            String  table = "SELECT * FROM wuliuyunfei a LEFT JOIN lctp b ON a.`code` = b.wuliucode WHERE b.pname = '" +
                    list1 +
                "' AND a.sendcity = '" + sendcity/*+"市"*/ +
                "' AND a.receiveprovince = '" + receiveprovince +
                "' AND a.receivecity = '" + receivecity  +
                "' AND a.wu = " + wu +
                "  AND a.wd = " + wd +
                "  AND a.flag = '"+flag+"'";
        List<tablepojo> addresult = wuliuDao.findtable(table);
        String ss= String.valueOf(addresult);
        System.out.println(ss);
        System.out.println(addresult);
        if (ss.equals("[]")){
            canadd.add(list1);
            int result= addwuliuservice.addwuliu(list1,fromprovince,sendcity,receiveprovince,receivecity,fww,fwp,wu,wd,wp,fw,discount,discounttype,flag);
            if (result== 1){

                List<tablepojo> addresult1 = wuliuDao.findtable(table);
                addresult1.get(0).getId();
                System.out.println("返回ID:"+addresult1.get(0).getId());
                canaddid.add(addresult1.get(0).getId());
            }

        }else {
            toedit.add(list1);
            for (tablepojo tlp:addresult
                 ) {
                toeditid.add(tlp.getId()) ;

            }
        }

        }

        Map<String,Object> map1 = new HashMap<>();
        map1.put("msg",1);
        map1.put("canadd",canadd);
        map1.put("toedit",toedit);
        map1.put("toeditid",toeditid);
        map1.put("canaddid",canaddid);
        JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map1));

        return wuliupojos;
    }

    @ResponseBody
    @RequestMapping("/addwuliuxishu")
    public JSONObject addwuliuxishu(String wuliucode,String wuliuname,String pname,String lctparabolic){
        System.out.println(wuliucode+wuliuname+lctparabolic+pname);
        Map<String,Object> map1 = new HashMap<>();
        String  table = "SELECT * FROM lctp where wuliucode = "+wuliucode +
                " and wuliuname = '"+wuliuname+"'" +
                " and pname = '"+pname+"'";
        List<xishupojo> tablelist = wuliuDao.findxishutable(table);
        System.out.println(tablelist);
        String ss= String.valueOf(tablelist);
        if (ss.equals("[]")){

            String  table1 = "INSERT into lctp(wuliucode,wuliuname,lctparabolic,pname) VALUES("+wuliucode+",'"+wuliuname+"',"+lctparabolic+",'"+pname+"')" ;
            int addresult = wuliuDao.addxishu(table1);
            System.out.println("插入条数："+addresult);
            if (addresult== 1){
                map1.put("result","插入成功！");
            }
        }else {
            map1.put("result","物流公司已存在，请在表格进行编辑");
        }


        map1.put("msg",1);

        JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map1));

        return wuliupojos;
    }

    @ResponseBody
    @RequestMapping("/getxishutable")
    public layuitablepojo getxishutable(){

        String  table = "SELECT * FROM lctp" ;
        List<xishupojo> tablelist = wuliuDao.findxishutable(table);
        System.out.println(tablelist);

        Map<String,Object> map = new HashMap<>();
        map.put("mag",1);
        map.put("result",tablelist);

        JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

        layuitablepojo layui = new layuitablepojo();
        layui.setCode(0);
        layui.setMsg("");
        layui.setCount((long)tablelist.size());
        layui.setData(tablelist);

        return layui;
    }


    @ResponseBody
    @RequestMapping("/upxishutable")
    public JSONObject upxishutable(String lctparabolic,String kdata,String sdata,String pdata,String ndata,int mode){
        if (mode == 1){
            String  table = "UPDATE lctp a set a.lctparabolic = "+lctparabolic+",a.pname = '"+pdata+"',a.wuliuname = '"+ndata+"' WHERE a.wuliucode = "+sdata ;
            int upresult = wuliuDao.upxishu(table);
            System.out.println(upresult);

            Map<String,Object> map = new HashMap<>();
            map.put("mag",1);
            map.put("result",upresult);

            JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

            return wuliupojos;
        }else {
            System.out.println(sdata);
            System.out.println(kdata);
            System.out.println(pdata);
            System.out.println(ndata);

            List<String> list = JSON.parseArray(sdata, String.class);
            List<String> klist = JSON.parseArray(kdata, String.class);
            List<String> plist = JSON.parseArray(pdata, String.class);
            List<String> nlist = JSON.parseArray(ndata, String.class);
            int upresult = 0;
            int tiao = 0;
            for (int i =0 ; list.size()>i;i++){

                String  table = "UPDATE lctp a set a.lctparabolic = "+klist.get(i)+",a.pname = '"+plist.get(i)+"',a.wuliuname = '"+nlist.get(i)+"' WHERE a.wuliucode = "+list.get(i) ;
                upresult = wuliuDao.upxishu(table);
                tiao++;
                System.out.println("共"+list.size()+"条；"+"更新第"+tiao+"条，"+"成功更新"+upresult+"条");

            }

            Map<String,Object> map = new HashMap<>();
            map.put("mag",1);
            map.put("result",tiao);

            JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

            return wuliupojos;

        }

    }

    @ResponseBody
    @RequestMapping("/upxishustatus")
    public JSONObject upxishustatus(String status,String wuliucode){

        String  table = "UPDATE lctp a set a.status = "+status+" WHERE a.wuliucode = "+wuliucode ;
        int upresult = wuliuDao.upxishu(table);
        System.out.println(upresult);

        Map<String,Object> map = new HashMap<>();
        map.put("mag",1);
        map.put("result",upresult);

        JSONObject wuliupojos = JSONObject.parseObject(JSON.toJSONString(map));

        return wuliupojos;
    }

    @ResponseBody
    @RequestMapping("/delxishutable")
    public JSONObject delxishutable(String lctparabolic,String sdata,int mode){
        if (mode == 1){
            String  table = "DELETE FROM lctp WHERE wuliucode = "+sdata ;
            System.out.println("即将执行的删除sql："+table);
            int delresult = wuliuDao.delxishu(table);
            System.out.println("删除条数："+delresult);

            String tableName = "lctp"; // 替换为实际的表名
            call_procedure.callWuliuyunfeiDeleteAction(tableName);
            System.out.println("重置id的存储过程已执行");

            Map<String,Object> map = new HashMap<>();
            map.put("mag",1);
            map.put("result","成功删除"+delresult+"条数据");

            JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));

            return result;
        }else {
            System.out.println(sdata);
            List<String> list = JSON.parseArray(sdata, String.class);
            String Sid = "";
            for (int i= 0 ;list.size()>i;i++){
                if (i==list.size()-1){
                    Sid=Sid+"'"+list.get(i)+"'";
                }else {
                    Sid=Sid+"'"+list.get(i)+"',";
                }

            }
            System.out.println(Sid);
            String  table = "DELETE FROM lctp WHERE wuliucode in ("+Sid +")";
            System.out.println("即将执行的删除sql："+table);
            int delresult = wuliuDao.delxishu(table);
            System.out.println("删除条数："+delresult);

            String tableName = "lctp"; // 替换为实际的表名
            call_procedure.callWuliuyunfeiDeleteAction(tableName);
            System.out.println("重置id的存储过程已执行");

            Map<String,Object> map = new HashMap<>();
            map.put("mag",1);
            map.put("result","成功删除"+delresult+"条数据");

            JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));

            return result;

        }


    }

    @ResponseBody
    @RequestMapping("/delwuliutable")
    public JSONObject delwuliutable(String sdata,int mode,boolean ISTablepageAllChoose,
                                    String selectvalue,
                                    String inputvalue,String exclude){
        System.out.println("ISTablepageAllChoose："+ISTablepageAllChoose);
        System.out.println("selectvalue："+selectvalue);
        System.out.println("inputvalue："+inputvalue);
        if (mode == 1){
            String  table = "DELETE FROM wuliuyunfei WHERE ID = "+sdata ;
            System.out.println("即将执行的删除sql："+table);
            int delresult = wuliuDao.delwuliutable(table);
            System.out.println("删除条数："+delresult);

            String tableName = "wuliuyunfei"; // 替换为实际的表名
            call_procedure.callWuliuyunfeiDeleteAction(tableName);
            System.out.println("重置id的存储过程已执行");

            Map<String,Object> map = new HashMap<>();
            map.put("mag",1);
            map.put("result","成功删除"+delresult+"条数据");

            JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));

            return result;
        }else {
            //是否勾选全选
            if (ISTablepageAllChoose){
                String where = "";

                if (selectvalue.length() > 0){
                    where = "WHERE  "+selectvalue+" like '"+inputvalue+"%' ";

                    if (exclude.length() > 0){
                        System.out.println(exclude);
                        List<String> list = JSON.parseArray(exclude, String.class);
                        String Sid = "";
                        for (int i= 0 ;list.size()>i;i++){
                            if (i==list.size()-1){
                                Sid=Sid+"'"+list.get(i)+"'";
                            }else {
                                Sid=Sid+"'"+list.get(i)+"',";
                            }

                        }
                        where = where + "AND ID not in ("+Sid+")";
                    }
                    String  table = "DELETE FROM wuliuyunfei " + where;
                    System.out.println("即将执行的删除sql："+table);
                    int delresult = wuliuDao.delwuliutable(table);
                    System.out.println("删除条数："+delresult);

                    String tableName = "wuliuyunfei"; // 替换为实际的表名
                    call_procedure.callWuliuyunfeiDeleteAction(tableName);
                    System.out.println("重置id的存储过程已执行");

                    Map<String,Object> map = new HashMap<>();
                    map.put("mag",1);
                    map.put("result","成功删除"+delresult+"条数据");

                    JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));

                    return result;

                }else {
                    Map<String,Object> map = new HashMap<>();
                    map.put("mag",1);
                    map.put("result","成功删除0条数据,批量删除只允许在有筛选条件的情况进行删除，请刷新界面重新选择需要删除的数据");

                    JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));

                    return result;

                }






            }else {

                System.out.println(sdata);
                List<String> list = JSON.parseArray(sdata, String.class);
                String Sid = "";
                for (int i= 0 ;list.size()>i;i++){
                    if (i==list.size()-1){
                        Sid=Sid+"'"+list.get(i)+"'";
                    }else {
                        Sid=Sid+"'"+list.get(i)+"',";
                    }

                }

                System.out.println(Sid);
                String  table = "DELETE FROM wuliuyunfei WHERE ID in ("+Sid+")" ;
                System.out.println("即将执行的删除sql："+table);
                int delresult = wuliuDao.delwuliutable(table);
                System.out.println("删除条数："+delresult);

                String tableName = "wuliuyunfei"; // 替换为实际的表名
                call_procedure.callWuliuyunfeiDeleteAction(tableName);
                System.out.println("重置id的存储过程已执行");

                Map<String,Object> map = new HashMap<>();
                map.put("mag",1);
                map.put("result","成功删除"+delresult+"条数据");

                JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));

                return result;
            }


        }


    }

    @ResponseBody
    @RequestMapping("/delAllwuliutable")
    public JSONObject delAllwuliutable(){

        String  table = "DELETE FROM wuliuyunfei" ;
        System.out.println("即将执行的删除sql："+table);
        int delresult = wuliuDao.delwuliutable(table);

        String tableName = "wuliuyunfei"; // 替换为实际的表名
        call_procedure.callWuliuyunfeiDeleteAction(tableName);
        System.out.println("重置id的存储过程已执行");

        Map<String,Object> map = new HashMap<>();
        map.put("mag",1);
        map.put("result","成功删除"+delresult+"条数据");
        JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));

        return result;
    }

    @RequestMapping(value = "/wuliu")
    public String view(Model model, HttpSession session){

        return "wuliu/wuliu";
    }
    @RequestMapping(value = "/jianyuetable")
    public String jianyuetable(Model model, HttpSession session){

        return "wuliu/jianyuetable";
    }

    @RequestMapping(value = "/xishutable")
    public String xishutable(Model model, HttpSession session){

        return "wuliu/xishutable";
    }

    @RequestMapping(value = "/wuliutable")
    public String wuliutable(Model model, HttpSession session){
        String  table = "SELECT * FROM lctp" ;
        List<xishupojo> tablelist = wuliuDao.findxishutable(table);
        System.out.println(tablelist);

        model.addAttribute("attrs",tablelist);//返回公司列表

        return "wuliu/wuliutable";
    }
}
